package com.testingedu.jdbcDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.testingedu.common.QueryResult;
import com.testingedu.model.TRegist;
import com.testingedu.utils.Format;

/**
 * 表TStudyState jdbc类 最后修改：2012-11-23 修改人:bfc
 * 
 * @author Administrator
 * 
 */
public class TRegistDao extends BaseDao<TRegist> {
	public TRegistDao() {
	}

	/**
	 * 增加学习状态
	 * 
	 * @param tStudyState
	 * @return
	 */
	public int add(TRegist tRegist) throws Exception{
		String sql = "insert into t_regist (name,phone,school,major,email,qq,sex,id_card,address,add_time) value(?,?,?,?,?,?,?,?,?,?)";
		List<Object> values = new ArrayList<Object>();
		values.add(tRegist.getName());
		values.add(tRegist.getPhone());
		values.add(tRegist.getSchool());
		if(tRegist.getSchool() == null || tRegist.getSchool().trim().length() == 0)
			tRegist.setSchool("");
		values.add(tRegist.getMajor());
		if(tRegist.getEmail() == null || tRegist.getEmail().trim().length() == 0)
			tRegist.setEmail("");
		values.add(tRegist.getEmail());
		if(tRegist.getQq() == null || tRegist.getQq().trim().length() == 0)
			tRegist.setQq("");
		values.add(tRegist.getQq());
		values.add(tRegist.getSex());
		if(tRegist.getIdCard() == null || tRegist.getIdCard().trim().length() == 0)
			tRegist.setIdCard("");
		values.add(tRegist.getIdCard());
		if(tRegist.getAddress() == null || tRegist.getAddress().trim().length() == 0)
			tRegist.setAddress("");
		values.add(tRegist.getAddress());
		values.add(Format.formatDateDay(new Date()));
		try {
			this.myJdbcSetValuesAndExecuteSaveOrUpdateOrDelete(sql, values);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return getMaxId();
	}

	private int getMaxId() throws Exception{
		String sql = "select max(id) as _id from t_regist";
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql, null);
			if (rs.next()) {
				return rs.getInt("_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
		return 0;
	}

	public void delete(int id) throws Exception{
		String sql = "delete from t_regist where id=?";
		try {
			this.myJdbcSetValuesAndExecuteDelete(sql, id);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			this.close();
		}
	}

	public void list(QueryResult queryResult, TRegist tRegist) throws Exception{
		StringBuffer sql = new StringBuffer(
				"select * from t_regist te where 1=1");
		List<Object> values = new ArrayList<Object>();
		if (tRegist != null) {
			if(tRegist.getName() != null && tRegist.getName().trim().length() > 0) {
				sql.append( " and te.name like ?");
				values.add("%"+tRegist.getName()+"%");
			}
			if(tRegist.getMajor() != null && tRegist.getMajor().trim().length() > 0) {
				sql.append(" and te.major like ?");
				values.add("%"+tRegist.getMajor()+"%");
			}
		}
		sql.append(" limit ?,?");
		List<Object> countValues = new ArrayList<Object>(values);
		values.add(queryResult.getStartRow());
		values.add(queryResult.getPageSize());
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.toString(), values);
			queryResult.setResultList(_resultSetToList(rs));
		} finally {
			close();
		}
		sql.replace(7, 8, "count(*)");
		try {
			this.myJdbcSetValuesAndExecuteQuery(sql.substring(0,
					sql.length() - 10), countValues);
			if (rs.next()) {
				queryResult.setAllCount(new Long(rs.getInt(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
	}

	public TRegist info(int id) throws Exception{
		String sql = "select * from t_regist te where te.id = ?";
		List<TRegist> result = null;
		try {
			this.myJdbcSetValuesAndExecuteLoad(sql, id);
			result = _resultSetToList(rs);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception();
		} finally {
			close();
		}
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public List<TRegist> _resultSetToList(ResultSet rs) throws Exception{
		List<TRegist> result = new ArrayList<TRegist>();
		try {
			while (rs.next()) {
				TRegist tRegist = new TRegist();
				tRegist.setId(rs.getInt("id"));
				tRegist.setAddTime(Format.formatDateDay(rs.getDate("add_time")));
				tRegist.setAddress(rs.getString("address"));
				tRegist.setEmail(rs.getString("email"));
				tRegist.setIdCard(rs.getString("id_card"));
				tRegist.setMajor(rs.getString("major"));
				tRegist.setPhone(rs.getString("phone"));
				tRegist.setSex(rs.getString("sex"));
				tRegist.setSchool(rs.getString("school"));
				tRegist.setQq(rs.getString("qq"));
				tRegist.setName(rs.getString("name"));
				result.add(tRegist);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return result;
	}
}
